package db.migration;

import com.example.vo.UserVo;
import org.jeecgframework.poi.excel.ExcelImportUtil;
import org.jeecgframework.poi.excel.entity.ImportParams;
import org.springframework.core.io.Resource;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.util.ResourceUtils;

import java.util.ArrayList;
import java.util.List;

/**
 * 使用java代码执行特殊文档的例子
 */
public class V1002__INIT_T_USER_INSERT_DATA extends BaseInitScript {

    /**
     * (non-Javadoc)
     * @see org.flywaydb.core.api.migration.spring.SpringJdbcMigration#migrate(org.springframework.jdbc.core.JdbcTemplate)
     */
    @Override
    public void migrate(JdbcTemplate jdbcTemplate) throws Exception {
        //定义脚本
        final String SQL = "insert into user (USER_ID, USER_NAME, AGE, CREATED_TIME, CREATED_BY, UPDATED_TIME, UPDATED_BY) values (?, ?, ?, '2022-03-23 09:47:52', '111', '2022-03-23 09:47:59', '111')";
        //查找资源
        Resource[] resources = new PathMatchingResourcePatternResolver().getResources(ResourceUtils.CLASSPATH_URL_PREFIX + "/data/用户.xls");
        ImportParams params = new ImportParams();
        params.setTitleRows(0);
        params.setHeadRows(1);
        //数据映射
        List<UserVo> list = ExcelImportUtil.importExcel(resources[0].getInputStream(), UserVo.class, params);
        List<Object[]> batchArgs = new ArrayList<>(list.size());
        for(UserVo vo : list) {
            //这边会自动匹配?按顺序放值即可
            batchArgs.add(new Object[]{vo.getUserId(), vo.getUserName(), vo.getAge()});
        }
        //保存数据
        jdbcTemplate.batchUpdate(SQL, batchArgs);
    }


}
